package TestModel;
use DBI qw(:sql_types);
use base 'DBI::Simply';
use strict;

__PACKAGE__->__SQL(
create => [] => <<'SQL' );
create table books(
  id     integer primary key,
  name   char(255),
  author char(255) )
SQL

__PACKAGE__->__SQL(
insert_book => [ SQL_INTEGER, SQL_CHAR, SQL_CHAR ] => <<'SQL' );
insert into books (id, name, author)
           values ( ?,    ?,      ?)
SQL

__PACKAGE__->__SQL(
all_books => [] => <<'SQL' );
select id, name, author
  from books
 order by id
SQL

__PACKAGE__->__SQL(
take_all => [] => <<'SQL' );
select * from books
SQL

__PACKAGE__->__SQL(
author_books => [ SQL_CHAR ] => <<'SQL' );
select id, name, author
  from books
 where author like ?
 order by id
SQL

package main;
use strict;
use Test::More tests => 14;
use Test::Deep;
use Test::Exception;
use DBD::SQLite;
use Data::Dumper;

BEGIN { use_ok( 'DBI::Simply' ); }

my $db = 'test.db';

my $m = TestModel->new( sub{ DBI->connect("dbi:SQLite:dbname=$db")
			       || die 'Cannot connect to DB';
			   }
		      );
$m->dbh->{AutoCommit} = 0;

ok( defined $m,
    'Create the model' );

lives_ok {
  $m->create->();
} 'Create table';

lives_ok {
  $m->insert_book->( 1, 'War and Peace', 'Leo Tolstoy' );
} 'Insert one';

$m->dbh->commit;

lives_ok {
  my $inserter = $m->insert_book; # prepare for many inserts
  $inserter->(@$_) for (
    [ 2,        'Anna Karenina',        'Leo Tolstoy' ],
    [ 3, 'Crime and Punishment', 'Fyodor Dostoyevsky' ],
  );
} 'Insert many';

$m->dbh->commit;

lives_ok {
  my $books = $m->author_books->('Leo%');
  cmp_deeply( $books->value(),
	      [ 1, 'War and Peace', 'Leo Tolstoy' ],
	      'select fist'
	    );
  cmp_deeply( $books->value(),
	      [ 2, 'Anna Karenina', 'Leo Tolstoy' ],
	      'select second'
	    );
  ok( $books->is_exhausted,
      'only two results'
    );
} 'check like';

lives_ok {
  my $books = $m->all_books->();
  cmp_deeply( $books->value(),
	      [ 1, 'War and Peace', 'Leo Tolstoy' ],
	      'select fist'
	    );
  cmp_deeply( $books->value(),
	      [ 2, 'Anna Karenina', 'Leo Tolstoy' ],
	      'select second'
	    );
  cmp_deeply( $books->value(),
	      [ 3, 'Crime and Punishment', 'Fyodor Dostoyevsky' ],
	      'select third'
	    );
  ok( $books->is_exhausted,
      'only three results'
    );
} 'check all';

$m->dbh->commit;
$m->dbh->disconnect;
unlink $db;
